#include "SQL/sql.h"


UserDatabase::UserDatabase(QObject *parent) : QObject(parent)
{
    static int connectionCount = 0;
    m_db = QSqlDatabase::addDatabase("QSQLITE", QString("user_connection_%1").arg(++connectionCount));
    //m_db = QSqlDatabase::addDatabase("QSQLITE", "user_connection"); // 使用自定义连接名
}

UserDatabase::~UserDatabase()
{
    closeDatabase();
}

// 打开数据库连接
bool UserDatabase::openDatabase(const QString &databaseName)
{
    m_db.setDatabaseName(databaseName);
    if (!m_db.open()) {
        qDebug() << "错误：无法打开数据库:";
        return false;
    }

    // 确保用户表存在
    if (!createTables()) {
        qDebug() << "错误：创建表失败";
        return false;
    }

    return true;
}

// 关闭数据库连接
void UserDatabase::closeDatabase()
{
    if (m_db.isOpen()) {
        m_db.close();
    }
}

// 检查数据库是否打开
bool UserDatabase::isOpen() const
{
    return m_db.isOpen();
}

// 创建用户表
bool UserDatabase::createTables()
{
    if (!isOpen()) return false;

    QSqlQuery query(m_db);
    return query.exec("CREATE TABLE IF NOT EXISTS users ("
                     "user_id INTEGER PRIMARY KEY AUTOINCREMENT, "   // 用户ID，主键，自增
                     "username TEXT NOT NULL UNIQUE, "               // 用户名，唯一，非空
                     "password TEXT NOT NULL, "                      // 密码，加密存储
                     "email TEXT UNIQUE, "                           // 邮箱，唯一
                     "phone TEXT UNIQUE, "                           // 手机号，唯一
                     "role TEXT NOT NULL DEFAULT 'user', "           // 用户角色，默认普通用户
                     "created_at TEXT NOT NULL, "                     // 注册时间
                     "updated_at TEXT NOT NULL)");                    // 最后更新时间
}

// 密码加密函数
QString UserDatabase::hashPassword(const QString &password)
{
    // 使用SHA-256加密密码
    QByteArray hash = QCryptographicHash::hash(
        password.toUtf8(),
        QCryptographicHash::Sha256
    );
    return QString(hash.toHex());
}

// 获取当前时间字符串
QString UserDatabase::getCurrentDateTime()
{
    return QDateTime::currentDateTime().toString(Qt::ISODate);
}

// 添加新用户
bool UserDatabase::addUser(const User &user)
{
    if (!isOpen()) return false;

    // 检查用户名是否已存在
    if (getUserByUsername(user.username).user_id != 0) {
        qDebug() << "错误：用户名已存在";
        return false;
    }

    // 检查邮箱是否已存在（如果提供了邮箱）
    if (!user.email.isEmpty() && getUserByEmail(user.email).user_id != 0) {
        qDebug() << "错误：邮箱已被注册";
        return false;
    }

    // 检查手机号是否已存在（如果提供了手机号）
    if (!user.phone.isEmpty() && getUserByPhone(user.phone).user_id != 0) {
        qDebug() << "错误：手机号已被注册";
        return false;
    }

    QString currentTime = getCurrentDateTime();
    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":username", user.username);
    params.insert(":password", hashPassword(user.password));
    params.insert(":email", user.email);
    params.insert(":phone", user.phone);
    params.insert(":role", user.role.isEmpty() ? "user" : user.role); // 默认角色为user
    params.insert(":created_at", currentTime);
    params.insert(":updated_at", currentTime);

    return prepareQuery(query,
                       "INSERT INTO users (username, password, email, phone, role, created_at, updated_at) "
                       "VALUES (:username, :password, :email, :phone, :role, :created_at, :updated_at)",
                       params);
}

// 删除用户(通过ID)
bool UserDatabase::deleteUser(int user_id)
{
    if (!isOpen()) return false;

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":user_id", user_id);

    return prepareQuery(query,
                       "DELETE FROM users WHERE user_id = :user_id",
                       params);
}

// 删除用户(通过用户名)
bool UserDatabase::deleteUserByUsername(const QString &username)
{
    if (!isOpen()) return false;

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":username", username);

    return prepareQuery(query,
                       "DELETE FROM users WHERE username = :username",
                       params);
}

// 更新用户信息(不包括密码)
bool UserDatabase::updateUser(const User &user)
{
    if (!isOpen()) return false;

    // 检查用户是否存在
    User existingUser = getUserById(user.user_id);
    if (existingUser.user_id == 0) {
        qDebug() << "错误：用户不存在";
        return false;
    }

    // 检查新用户名是否已被其他用户使用
    if (user.username != existingUser.username && getUserByUsername(user.username).user_id != 0) {
        qDebug() << "错误：用户名已被其他用户使用";
        return false;
    }

    // 检查新邮箱是否已被其他用户使用
    if (user.email != existingUser.email && !user.email.isEmpty() && getUserByEmail(user.email).user_id != 0) {
        qDebug() << "错误：邮箱已被其他用户使用";
        return false;
    }

    // 检查新手机号是否已被其他用户使用
    if (user.phone != existingUser.phone && !user.phone.isEmpty() && getUserByPhone(user.phone).user_id != 0) {
        qDebug() << "错误：手机号已被其他用户使用";
        return false;
    }

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":user_id", user.user_id);
    params.insert(":username", user.username);
    params.insert(":email", user.email);
    params.insert(":phone", user.phone);
    params.insert(":role", user.role);
    params.insert(":updated_at", getCurrentDateTime());

    return prepareQuery(query,
                       "UPDATE users SET username = :username, email = :email, "
                       "phone = :phone, role = :role, updated_at = :updated_at "
                       "WHERE user_id = :user_id",
                       params);
}

// 更新用户密码
bool UserDatabase::updateUserPassword(const QString &username, const QString &newPassword)
{
    if (!isOpen()) return false;

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":username", username);
    params.insert(":password", hashPassword(newPassword));
    params.insert(":updated_at", getCurrentDateTime());

    return prepareQuery(query,
                       "UPDATE users SET password = :password, updated_at = :updated_at "
                       "WHERE username = :username",
                       params);
}

// 用户认证
bool UserDatabase::authenticateUser(const QString &username, const QString &password)
{
    User user = getUserByUsername(username);
    if (user.user_id == 0) {
        return false; // 用户不存在
    }

    // 比较加密后的密码
    return user.password == hashPassword(password);
}

// 获取所有用户
QVector<UserDatabase::User> UserDatabase::getAllUsers()
{
    QVector<User> users;
    if (!isOpen()) return users;

    QSqlQuery query("SELECT user_id, username, password, email, phone, role, created_at, updated_at FROM users", m_db);
    while (query.next()) {
        User user;
        user.user_id = query.value("user_id").toInt();
        user.username = query.value("username").toString();
        user.password = query.value("password").toString();
        user.email = query.value("email").toString();
        user.phone = query.value("phone").toString();
        user.role = query.value("role").toString();
        user.created_at = query.value("created_at").toString();
        user.updated_at = query.value("updated_at").toString();
        users.append(user);
    }

    return users;
}

// 通过ID获取用户
UserDatabase::User UserDatabase::getUserById(int user_id)
{
    User user;
    if (!isOpen()) return user;

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":user_id", user_id);

    if (prepareQuery(query,
                    "SELECT user_id, username, password, email, phone, role, created_at, updated_at "
                    "FROM users WHERE user_id = :user_id",
                    params)) {
        if (query.next()) {
            user.user_id = query.value("user_id").toInt();
            user.username = query.value("username").toString();
            user.password = query.value("password").toString();
            user.email = query.value("email").toString();
            user.phone = query.value("phone").toString();
            user.role = query.value("role").toString();
            user.created_at = query.value("created_at").toString();
            user.updated_at = query.value("updated_at").toString();
        }
    }

    return user;
}

// 通过用户名获取用户
UserDatabase::User UserDatabase::getUserByUsername(const QString &username)
{
    User user;
    if (!isOpen()) return user;

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":username", username);

    if (prepareQuery(query,
                    "SELECT user_id, username, password, email, phone, role, created_at, updated_at "
                    "FROM users WHERE username = :username",
                    params)) {
        if (query.next()) {
            user.user_id = query.value("user_id").toInt();
            user.username = query.value("username").toString();
            user.password = query.value("password").toString();
            user.email = query.value("email").toString();
            user.phone = query.value("phone").toString();
            user.role = query.value("role").toString();
            user.created_at = query.value("created_at").toString();
            user.updated_at = query.value("updated_at").toString();
        }
    }

    return user;
}

// 通过邮箱获取用户
UserDatabase::User UserDatabase::getUserByEmail(const QString &email)
{
    User user;
    if (!isOpen()) return user;

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":email", email);

    if (prepareQuery(query,
                    "SELECT user_id, username, password, email, phone, role, created_at, updated_at "
                    "FROM users WHERE email = :email",
                    params)) {
        if (query.next()) {
            user.user_id = query.value("user_id").toInt();
            user.username = query.value("username").toString();
            user.password = query.value("password").toString();
            user.email = query.value("email").toString();
            user.phone = query.value("phone").toString();
            user.role = query.value("role").toString();
            user.created_at = query.value("created_at").toString();
            user.updated_at = query.value("updated_at").toString();
        }
    }

    return user;
}

// 通过手机号获取用户
UserDatabase::User UserDatabase::getUserByPhone(const QString &phone)
{
    User user;
    if (!isOpen()) return user;

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":phone", phone);

    if (prepareQuery(query,
                    "SELECT user_id, username, password, email, phone, role, created_at, updated_at "
                    "FROM users WHERE phone = :phone",
                    params)) {
        if (query.next()) {
            user.user_id = query.value("user_id").toInt();
            user.username = query.value("username").toString();
            user.password = query.value("password").toString();
            user.email = query.value("email").toString();
            user.phone = query.value("phone").toString();
            user.role = query.value("role").toString();
            user.created_at = query.value("created_at").toString();
            user.updated_at = query.value("updated_at").toString();
        }
    }

    return user;
}

// 执行SQL查询
bool UserDatabase::executeQuery(QSqlQuery &query)
{
    if (!query.exec()) {
        qDebug() << "查询错误:";
        qDebug() << "执行的SQL:" << query.lastQuery();
        return false;
    }
    return true;
}

// 准备SQL查询并绑定参数
bool UserDatabase::prepareQuery(QSqlQuery &query, const QString &sql, const QVariantMap &params)
{
    if (!query.prepare(sql)) {
        qDebug() << "准备查询错误:";
        return false;
    }

    for (auto it = params.constBegin(); it != params.constEnd(); ++it) {
        query.bindValue(it.key(), it.value());
    }

    return executeQuery(query);
}

bool UserDatabase::verifyUserIdentity(const QString &username, const QString &phone)
{
    if (!isOpen()) return false;

    QSqlQuery query(m_db);
    QVariantMap params;
    params.insert(":username", username);
    params.insert(":phone", phone);

    if (prepareQuery(query,
                    "SELECT user_id FROM users "
                    "WHERE username = :username AND phone = :phone",
                    params)) {
        return query.next(); // 如果找到匹配记录返回true
    }
    return false;
}
